﻿using MSharp.Data.DatabaseInfo;
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Text.RegularExpressions;

namespace MSharp.Data.Database
{
    public class MySqlDB : DB
    {
        public MySqlDB(DBType dbType, DbProviderFactory dbFactory, string ConnectionString)
            : base(dbType, dbFactory, ConnectionString)
        {
            this.Info = new MySqlDBInfo(this);
        }

        public override DataTable GetDataTableByPager(int currentPage, int pageSize, string selColumns, string joinTableName, string whereStr, string orderbyStr, out long totalCount)
        {
            if (string.IsNullOrEmpty(selColumns))
            {
                selColumns = "*";
            }

            if (currentPage <= 0)
            {
                currentPage = 1;
            }

            if (pageSize <= 0)
            {
                pageSize = 50;
            }

            string cntSQL = string.Empty, strPageSQL = string.Empty;
            DataTable data = new DataTable();
            totalCount = 0;

            if (!string.IsNullOrWhiteSpace(whereStr))
            {
                whereStr = Regex.Replace(whereStr, @"(\s)*(where)?(\s)*(.+)", "and $3$4", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }

            if (!string.IsNullOrWhiteSpace(orderbyStr))
            {
                orderbyStr = Regex.Replace(orderbyStr, @"(\s)*(order)(\s)+(by)(.+)", "$5", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            }
            else
            {
                throw new ArgumentNullException("orderbyStr");
            }


            cntSQL = "select count(1) from {0} where 1=1 {1}";
            cntSQL = string.Format(cntSQL, joinTableName, whereStr);

            string strSQL = "select {0} from {1} where 1=1 {2} {3} ";
            strSQL = string.Format(strSQL, selColumns, joinTableName, whereStr, orderbyStr);

            strPageSQL = string.Format(@"SELECT * FROM ({0}) A limit {1} {2}",
                                       strSQL, (currentPage - 1) * pageSize + 1, (currentPage) * pageSize);

            DataSet ds = new DataSet("ds");
            using (DbConnection conn = CreateConn())
            {
                DbCommand cmd = conn.CreateCommand();
                try
                {
                    cmd.CommandText = strPageSQL;
                    PrepareCommand(cmd, conn, null, cntSQL, (object)null, 300);
                    DataAdapter adapter = CreateAdapter(cmd);
                    adapter.Fill(ds);

                    if (ds.Tables.Count > 0)
                    {
                        data = ds.Tables[0];
                    }

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, data);
                    }

                    cmd.CommandText = cntSQL;
                    PrepareCommand(cmd, conn, null, cntSQL, (object)null, 300);
                    totalCount = cmd.ExecuteScalar().ChangeType<long>();

                    if (OnExecuted != null)
                    {
                        OnExecuted.Invoke(cmd.CommandText, cmd.Parameters, totalCount);
                    }
                }
                catch (Exception ex)
                {
                    if (this.OnError != null)
                        this.OnError.Invoke(cmd.CommandText, cmd.Parameters, ex);
                    throw ex;
                }
            }
            return data;
        }
    }
}
